BigQueryのジョブのラベル機能が便利でした（小ネタ）
クエリジョブにラベルを付与し、bqコマンドやINFORMATION_SCHEMAから参照する。
データアナリティクス事業本部、池田です。
BigQueryでの作業をした際に、ジョブラベルがとても便利だと感じたのでブログにします。
ラベルについて
BigQueryでは 各リソースにラベルをつける
ことができるそうです。
ラベルの対象はデータセット テーブル ビュー ジョブ で、
今回はジョブに付与するラベルについて触れます。
ジョブラベルは他のリソースに付与するラベルよりも制限があり、 あとから変更できなかったり、タグに変換できなかったりします。 また、ラベル自体が課金対象となるようです。
ジョブにラベルを追加すると、そのラベルが課金データとしてカウントされます。
使ってみる
ジョブラベルはコンソールからのクエリ実行では付与できないので、
bqコマンド
の query から実行してみます。
--label オプションに「KEY:VALUE」の形で設定します。
こんな感じ↓
bq --location=US query \ --use_legacy_sql=false \ --use_cache=false \ --label "department:shipping" \ 'SELECT COUNT(*) AS cnt FROM `bigquery-public-data.samples.natality`'
ラベルに利用部門を設定するイメージです。
今回は BigQueryの一般公開データセット
から出生データの natality サンプルテーブルを参照しています。
↓ Cloud Shell から実行しました。
ジョブのIDを使って、 bqコマンド
の show からジョブの情報を参照しラベルを表示できます。
（一番右に Labels 列があります。）
$ bq show -j --format=pretty {ジョブのID}
+----------+---------+-----------------+----------------+------------------------------+-----------------+--------------+--------------+---------------------+
| Job Type | State | Start Time | Duration | User Email | Bytes Processed | Bytes Billed | Billing Tier | Labels |
+----------+---------+-----------------+----------------+------------------------------+-----------------+--------------+--------------+---------------------+
| query | SUCCESS | 06 Apr 07:11:38 | 0:00:00.872000 | foo@bar | 0 | 0 | 0 | department:shipping |
+----------+---------+-----------------+----------------+------------------------------+-----------------+--------------+--------------+---------------------+
また、 INFORMATION_SCHEMA の JOBS_BY_* ビュー にもラベルは含まれ、抽出条件としても使用できます。
【 INFORMATION_SCHEMA を使用したジョブ メタデータの取得 】
labels カラムはRECORD型なので、 SAFE_OFFSET() などで↓こんな感じで取得します。
SELECT
labels[SAFE_OFFSET(0)].value AS dept,
creation_time, job_id, start_time, end_time,
REGEXP_REPLACE(LEFT(query, 100), '\r?\n', ' ') AS query
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
creation_time >= '2021-04-06 07:00:00' -- UTC, partition
AND labels[SAFE_OFFSET(0)].key = 'department'
ORDER BY creation_time DESC;
（部門情報を持つジョブを抽出するイメージ）
↓
+----------+---------------------+--------------------------------------------+---------------------+---------------------+---------------------------------------------------------------------+ | dept | creation_time | job_id | start_time | end_time | query | +----------+---------------------+--------------------------------------------+---------------------+---------------------+---------------------------------------------------------------------+ | shipping | 2021-04-06 07:11:37 | foo | 2021-04-06 07:11:38 | 2021-04-06 07:11:39 | SELECT COUNT(*) AS cnt FROM `bigquery-public-data.samples.natality` | +----------+---------------------+--------------------------------------------+---------------------+---------------------+---------------------------------------------------------------------+
特に私が便利だと感じたのは、クエリのチューニングやパフォーマンスの確認で、 たくさんある結果を整理する時でした。
↓こんな感じでテスト内容が分かるようにラベルをつけておいて…
bq --location=US query \ --use_legacy_sql=false \ --use_cache=false \ --label "title:test1" \ --label "case:male" \ 'SELECT COUNT(*) AS cnt FROM `bigquery-public-data.samples.natality` WHERE is_male IS true' bq --location=US query \ --use_legacy_sql=false \ --use_cache=false \ --label "title:test1" \ --label "case:not_male" \ 'SELECT COUNT(*) AS cnt FROM `bigquery-public-data.samples.natality` WHERE is_male IS false'
↓こんな感じのクエリで所要時間やスロットの使われ方を確認する時に ラベルがあるとだいぶ探しやすく・見やすいです。
SELECT
labels[SAFE_OFFSET(0)].value AS title, labels[SAFE_OFFSET(1)].value AS `case`,
ROUND(TIMESTAMP_DIFF(end_time, start_time, MILLISECOND) / 1000, 1) AS time_sec,
ROUND(total_slot_ms / TIMESTAMP_DIFF(end_time, start_time, MILLISECOND)) AS avg_slots,
total_slot_ms, total_bytes_billed, cache_hit,
(error_result IS NOT NULL) AS has_error,
creation_time, job_id, priority, start_time, end_time,
state, total_bytes_processed,
REGEXP_REPLACE(LEFT(query, 100), '\r?\n', ' ') AS query
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
creation_time >= '2021-04-06 07:00:00' -- UTC, partition
AND labels[SAFE_OFFSET(0)].value LIKE 'test%'
ORDER BY title, `case`;
↓
+-------+----------+----------+-----------+---------------+--------------------+-----------+-----------+---------------------+--------------------------------------------+-------------+---------------------+---------------------+-------+-----------------------+---------------------------------------------------------------------------------------------+ | title | case | time_sec | avg_slots | total_slot_ms | total_bytes_billed | cache_hit | has_error | creation_time | job_id | priority | start_time | end_time | state | total_bytes_processed | query | +-------+----------+----------+-----------+---------------+--------------------+-----------+-----------+---------------------+--------------------------------------------+-------------+---------------------+---------------------+-------+-----------------------+---------------------------------------------------------------------------------------------+ | test1 | male | 0.9 | 11.0 | 9869 | 138412032 | false | false | 2021-04-06 07:17:03 | foo | INTERACTIVE | 2021-04-06 07:17:03 | 2021-04-06 07:17:04 | DONE | 137826763 | SELECT COUNT(*) AS cnt FROM `bigquery-public-data.samples.natality` WHERE is_male IS true | | test1 | not_male | 0.8 | 17.0 | 13818 | 138412032 | false | false | 2021-04-06 07:17:27 | bar | INTERACTIVE | 2021-04-06 07:17:27 | 2021-04-06 07:17:28 | DONE | 137826763 | SELECT COUNT(*) AS cnt FROM `bigquery-public-data.samples.natality` WHERE is_male IS false | +-------+----------+----------+-----------+---------------+--------------------+-----------+-----------+---------------------+--------------------------------------------+-------------+---------------------+---------------------+-------+-----------------------+---------------------------------------------------------------------------------------------+
state カラムは PENDING, RUNNING, DONE の状態だけ（エラーは無い）のようで、
クエリが成功しているのかどうかは error_result カラムで確認するようにしています。
（試した限りはそれでたぶん判別できそう…）
おわりに
いろいろなクラウドのサービスがありますが、 タグやラベルの機能がある場合にそれを使っていると、 なんとなくおしゃれな感じがしませんでしょうか？ 気のせいかもしれません。
